﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web.Configuration;

namespace Utility1
{
    public class Xuly
    {
        public SqlConnection sqlCn;
        private string strErrorMessage;
        private int intErrorNumber;

        private string sConnectionString;
        public Xuly()
        {

        }

        public string ConnectionString
        {
            get
            {
                return sConnectionString;
            }
            set
            {
                sConnectionString = value;
            }
        }
        // Ham mo ket noi
        public void OpenConnection(String str)
        {
            sConnectionString = WebConfigurationManager.ConnectionStrings[str].ToString();

        }
        #region Connect Data
        public SqlConnection openConnect()
        {
            SqlConnection sqlCn = new SqlConnection(ConnectionString);
            try
            {
                sqlCn.Open();
            }
            catch
            {
                return null;
            }
            return sqlCn;
        }

        public void Disconnect(SqlConnection sqlCn)
        {
            if (sqlCn != null)
            {
                if (sqlCn.State == ConnectionState.Open)
                    sqlCn.Close();
                sqlCn.Dispose();
            }
        }

        #endregion

        /// <summary>
        /// String: Câu thông báo lỗi khi truy cập SQL
        /// </summary>
        /// 
        public string ErrorMessage
        {
            get
            {
                return strErrorMessage;
            }
        }
        /// <summary>
        /// int: Số lỗi khi truy cập SQL
        /// </summary>
        public int ErrorNumber
        {
            get
            {
                return intErrorNumber;
            }
        }

        /// <summary>
        /// Thực thi 1 StoreProcedure có trả về dữ liệu là 1 bảng
        /// </summary>
        /// <return>
        /// DataTable
        /// </return>>
        public DataTable ExcuteStoreTable(string TenStoreProcedure, SqlParameter[] sqlParam)
        {
            DataTable dtbTmp = new DataTable();
            OpenConnection("reselleradmin_thvConnectionString");
            try
            {
                sqlCn = openConnect();
                SqlCommand sqlCmd = new SqlCommand();
                sqlCmd.CommandTimeout = 2000;
                sqlCmd.Connection = sqlCn;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = TenStoreProcedure;
                for (int i = 0; i < sqlParam.Length; i++)
                {
                    sqlCmd.Parameters.Add(sqlParam[i]);
                }
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = sqlCmd;
                sqlDA.Fill(dtbTmp);
                Disconnect(sqlCn);
            }
            catch (SqlException sqlEx)
            {
                strErrorMessage = sqlEx.Message;
                intErrorNumber = sqlEx.Number;
            }
            finally
            {
                if (sqlCn.State == ConnectionState.Open)
                    sqlCn.Close();
                sqlCn.Dispose();

            }
            return dtbTmp;
        }

        public DataTable GetInformationByID(int intID, String columname, String Proc)
        {
            SqlParameter[] arrParam = 
            {
                new SqlParameter(columname, SqlDbType.Int) 
            };
            arrParam[0].Value = intID;
            return ExcuteStoreTable(Proc, arrParam);
        }
        /// <summary>
        /// Thực thi 1 câu lệnh SQL dạng select có trả về dữ liệu là 1 bảng
        /// </summary>
        /// <return>
        /// DataTable
        /// </return>>
        public DataTable ExcuteSQL_Table(string strSQL)
        {
            OpenConnection("reselleradmin_thvConnectionString");
            sqlCn = openConnect();
            SqlDataAdapter Adapter = new SqlDataAdapter(strSQL, sqlCn);
            DataTable ds = new DataTable();
            try
            {
                Adapter.Fill(ds);
                Disconnect(sqlCn);
            }
            catch (SqlException E)
            {
                string strDescriptionError = E.Message;
            }
            finally
            {
                if (sqlCn.State == ConnectionState.Open)
                    sqlCn.Close();
                sqlCn.Dispose();
            }
            return ds;

        }

        public DataTable ExcuteSQL_Table_tintuc(string strSQL)
        {
            OpenConnection("reselleradmin_ms_thvConnectionString");
            sqlCn = openConnect();
            SqlDataAdapter Adapter = new SqlDataAdapter(strSQL, sqlCn);
            DataTable ds = new DataTable();
            try
            {
                Adapter.Fill(ds);
                Disconnect(sqlCn);
            }
            catch (SqlException E)
            {
                string strDescriptionError = E.Message;
            }
            finally
            {
                if (sqlCn.State == ConnectionState.Open)
                    sqlCn.Close();
                sqlCn.Dispose();
            }
            return ds;

        }

        public DataTable SelectCategory(string CateName)
        {
            return ExcuteSQL_Table(CateName);
        }
        public DataTable GetInfo(String Clum, String id, String proc)
        {
            SqlParameter[] arrParam = 
            {
                new SqlParameter(Clum, SqlDbType.NVarChar) 
            };
            arrParam[0].Value = id;
            return ExcuteStoreTable(proc, arrParam);

        }
    }
}
